#!/usr/bin/python

import time
import random
import hashlib
import datetime
from threading import Thread, Lock
import pg	#python-pygresql



mutex = Lock()


class psqlManager():
	def __init__(self, dbname2, host2, user2, passwd2):
		self.dbname2 = dbname2
		self.host2 = host2
		self.user2 = user2
		self.passwd2 = passwd2

		self.conn = pg.connect(dbname=dbname2, host=host2, user=user2, passwd=passwd2)
		self.psqlConnection = pg.DB(dbname=dbname2, host=host2, user=user2, passwd=passwd2)

		self.remoteDb = True
		#print self.psqlConnection.status

	def close(self):
		self.conn.close()

	def uuid(self):
		t = long( time.time() * 1000 )
		r = long( random.random()*100000000000000000L )
		try:
			a = socket.gethostbyname( socket.gethostname() )
		except:
			# if we can't get a network address, just imagine one
			a = random.random()*100000000000000000L
		data = str(t)+' '+str(r)+' '+str(a)+' '
		data = hashlib.md5(data).hexdigest()
		return data


	def getResInfo(self, var, userDomain):
		try:
			websettings = self.query("select " + var + " from res_dat where resname='" + userDomain + "';")
			websettings = websettings[0]
	
			return websettings
		except:
			raise Exception('This restaurant ' + userDomain + ' does not exist')

	def initializeOrderColumns(self):
		self.conn.query("ALTER TABLE meis ADD COLUMN uniqueKey varchar(32);")
		self.conn.query("ALTER TABLE meis ADD COLUMN date varchar(11);")
		self.conn.query("ALTER TABLE meis ADD COLUMN orderDetail text;")
		self.conn.query("ALTER TABLE meis ADD COLUMN price real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN resName varchar(30);")
		self.conn.query("ALTER TABLE meis ADD COLUMN address varchar(30);")
		self.conn.query("ALTER TABLE meis ADD COLUMN entry_by varchar(30);")
		self.conn.query("ALTER TABLE meis ADD COLUMN hour_string varchar(10);")
		self.conn.query("ALTER TABLE meis ADD COLUMN table_description varchar(100);")
		self.conn.query("ALTER TABLE meis ADD COLUMN status varchar(20);")
		self.conn.query("ALTER TABLE meis ADD COLUMN submit_source_type varchar(20);")
		self.conn.query("ALTER TABLE meis ADD COLUMN menuType varchar(10);")
		self.conn.query("ALTER TABLE meis ADD COLUMN tip real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN email varchar(30);")
		self.conn.query("ALTER TABLE meis ADD COLUMN delivery_charge real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN extra_info varchar(100);")
		self.conn.query("ALTER TABLE meis ADD COLUMN food_sum real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN food_tax real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN pre_calculated_price real;")
		self.conn.query("ALTER TABLE meis ADD COLUMN order_key varchar(10);")
		self.conn.query("ALTER TABLE meis ADD COLUMN order_id integer;")
		self.conn.query("ALTER TABLE meis ADD COLUMN delivery_range integer;")
		self.conn.query("ALTER TABLE meis ADD COLUMN delivery_cost_per_mile real;")

	def crashReportOpen(self, fname):
		pass
		#print('-----------------open---------   ' + fname)

	def crashReportClose(self, fname):
		pass
		#print('-----------------close---------   ' + fname)

	def addColumn(self, resName, columnName, typeString):
		self.crashReportOpen('addColumn')
		mutex.acquire()
		try:
			cmd = "ALTER TABLE " + resName + " ADD COLUMN " + columnName + " " + typeString + ";"
			por = self.psqlConnection.query(cmd)
		finally:
			print('Add column failed')
			mutex.release()

		self.crashReportClose('addColumn')


	def insertTable(self, tableName, dataDictionary):
		self.crashReportOpen('insertTable')

		mutex.acquire()
		try:
			utc_datetime = datetime.datetime.utcnow()
			dataDictionary['last_edit'] = utc_datetime.strftime("%Y-%m-%d %H:%M:%S")

			self.psqlConnection.insert(tableName, dataDictionary)
		finally:
			mutex.release()

		self.crashReportClose('insertTable')

	def delete(self, tableName, dataDictionary):
		self.crashReportOpen('delete')

		mutex.acquire()
		try:
			self.psqlConnection.delete(tableName, dataDictionary)
		finally:
			mutex.release()

		self.crashReportClose('delete')

	#THis is deprecated function
	def insertOrder(self, tableName, dataDictionary):
		self.crashReportOpen('insertOrder')

		mutex.acquire()
		try:
			self.psqlConnection.insert(tableName, dataDictionary)
		finally:
			mutex.release()


	def deleteOrder(self, tableName, dataDictionary):
		self.crashReportOpen('deleteOrder')

		mutex.acquire()
		try:
			self.psqlConnection.delete(tableName, dataDictionary)
		finally:
			mutex.release()

		self.crashReportClose('deleteOrder')

	def updateTable(self, tableName, dataDictionary):
		self.crashReportOpen('updateTable')

		retVal = True
		mutex.acquire()
		try:
		 	utc_datetime = datetime.datetime.utcnow()
		 	dataDictionary['last_edit'] = utc_datetime.strftime("%Y-%m-%d %H:%M:%S")

			try:
  			 	self.psqlConnection.update(tableName, dataDictionary)
  			 	self.crashReportClose('updateTable')
			except:
				try:
					self.psqlConnection.insert(tableName, dataDictionary)
				except:
					retVal = False

		finally:
			mutex.release()
			return retVal

	def updateOrder(self, tableName, dataDictionary):
		mutex.acquire()
		passed = True
		try:
			try:
				self.psqlConnection.update(tableName, dataDictionary)
			except:
				passed = False
		finally:
			mutex.release()

		return passed

	def itemExists(self, tableName, orderUUID):
		mutex.acquire()
		try:
			try:
				self.psqlConnection.get(tableName, orderUUID)
				return True
			except:
				return False
		finally:
			mutex.release()

	def getRow(self, tableName, uuid):
		mutex.acquire()
		try:
			try:
				vss = self.psqlConnection.get(tableName, uuid)
				return vss
			except:
				return {}
		finally:
			mutex.release()


	def getRowParts(self, parts, tableName, specification):
		try:
			self.crashReportOpen('getRowParts')

			cmd = "select " + parts + " from " + tableName + " where " + specification + ';'
			vvs = self.query(cmd)[0]

			self.crashReportClose('getRowParts')
			return vvs
		except:
			return {}



	def getConfirmOrder(self, tableName, orderUUID):
		try:
			self.crashReportOpen('getOrder')

			cmd = "select uuid, resname, status from meis where "
			cmd += "confirm_uuid='" + orderUUID + "';"
			vvs = self.query(cmd)[0]

			self.crashReportClose('getOrder')
			return vvs
		except:
			return {}

	def getOrder(self, tableName, orderUUID):
		try:
			self.crashReportOpen('getOrder')

			cmd = "select * from meis where "
			cmd += "uuid='" + orderUUID + "';"
			vvs = self.query(cmd)[0]

			self.crashReportClose('getOrder')
			return vvs
		except:
			return {}

	def query2(self, command):
		mutex.acquire()
		try:
			self.verifyConnection()
			val = self.conn.query(command)
			return val
		finally:
			mutex.release()
		
	def verifyConnection(self):
		if self.conn.status != 1:
			try: self.close()
			except: pass
			self.conn = pg.connect(dbname=self.dbname2, host=self.host2, user=self.user2, passwd=self.passwd2)


	def query(self, command):
		mutex.acquire()
		try:
			#self.conn.reset()
			self.verifyConnection()
			val = self.conn.query(command)
			return val.dictresult()
		finally:
			mutex.release()
			

	def deleteAllOrders(self):
		self.crashReportOpen('deleteAllOrders')

		cmd = "select * from meis;"
		val = self.query(cmd)
		for p in val:
			self.psqlConnection.delete('meis', p)

		self.crashReportClose('deleteAllOrders')

	def deleteEmptyOrders(self):
		self.crashReportOpen('deleteEmptyOrders')

		cmd = "delete from meis where status='customer_login';"
		val = self.conn.query(cmd)

		self.crashReportClose('deleteEmptyOrders')

	def getCurrentOrderID(self, resname, today):
		self.crashReportOpen('getCurrentOrderID')

		cmd = "select order_id from meis where "
		cmd += "resname='" + resname + "' and "
		cmd += "date='" + today + "' order by order_id desc;"
		result = self.query(cmd)

		self.crashReportClose('getCurrentOrderID')

		if len(result) == 0: return 0
		else: return result[0]['order_id']

	def getAllUsersVal(self, resname, val):
		self.crashReportOpen('getAllUsersVal')

		cmd = "select " + val + " from restaurant_users where "
		cmd += "resname='" + resname + "';"
		self.crashReportClose('getAllUsersVal')
		return self.query(cmd)


	def getAllUsers(self, resname):
		self.crashReportOpen('getAllUsers')

		cmd = "select user_name from restaurant_users where "
		cmd += "resname='" + resname + "' and access<>'root';"

		self.crashReportClose('getAllUsers')
		return self.query(cmd)


	def getUserVal(self, resname, username, val):
		self.crashReportOpen('getUserVal')

		cmd = "select " + val + " from restaurant_users where "
		cmd += "resname='" + resname + "' and "
		cmd += "user_name='" + username.strip() + "';"
		cmd = str(cmd)
	
		val = self.query(cmd)
		val = val[0]
		self.crashReportClose('getUserVal')
		return val


	def getUser(self, resname, username):
		self.crashReportOpen('getUser')

		cmd = "select * from restaurant_users where "
		cmd += "resname='" + resname + "' and "
		cmd += "user_name='" + username + "';"
		try:
			#self.conn.reset()
			vv2 = self.query(cmd)[0]
			self.crashReportClose('getUser')
		
			return vv2
		except:
			b = {}
			b['rand_key'] = 'expired'
			self.crashReportClose('getUser')
			return b

	def addUser(self, userData):
		self.crashReportOpen('addUser')

		mutex.acquire()
		try:
			self.psqlConnection.insert('restaurant_users', userData)
		finally:
			mutex.release()

		self.crashReportClose('addUser')


	def getAllOrdersTodayByRestaurant(self, today, resname):
		self.crashReportOpen('getAllOrdersToday')

		cmd = "select order_detail from meis where "
		cmd += "date='" + today + "' and resname='" + resname + "' and "
		cmd += "submit_source_type<>'unknown';"
		val = self.query(cmd)

		self.crashReportClose('getAllOrdersToday')
		return val


	def getAllOrdersToday(self, today):
		self.crashReportOpen('getAllOrdersToday')

		cmd = "select order_detail from meis where "
		cmd += "date='" + today + "' and "
		cmd += "submit_source_type<>'unknown';"
		val = self.query(cmd)

		self.crashReportClose('getAllOrdersToday')
		return val

	def deleteTodayFailedOrder(self, today):
		self.crashReportOpen('deleteTodayFailedOrder')

		cmd = "select * from meis where "
		cmd += "date='" + today + "' and "
		cmd += "submit_source_type='unknown';"
		val = self.query(cmd)
		for p in val:
			self.psqlConnection.delete('meis', p)

		self.crashReportClose('deleteTodayFailedOrder')

	def deleteAllFailedOrder(self, today):
		self.crashReportOpen('deleteAllFailedOrder')

		cmd = "select * from meis where "
		cmd += "date<>'" + today + "' and "
		cmd += "submit_source_type='unknown';"
		val = self.query(cmd)
		for p in val:
			self.psqlConnection.delete('meis', p)

		self.crashReportClose('deleteAllFailedOrder')

	def getAllPricePerDay(self, resName, today):
		self.crashReportOpen('getAllPricePerDay')

		cmd = "select price from meis where "
		cmd += "date='" + today + "' and status<>'customer_login' and status<>'temporary' and "
		cmd += "resname='" + resName + "'"
		cmd += " order by order_id desc"
		cmd += ";"
		#self.conn.reset()
		result = self.conn.query(cmd)

		self.crashReportClose('getAllPricePerDay')
		return result.getresult()

	def getAllNeedReviewOrders(self, resName):
		self.crashReportOpen('getAllDayOrders')

		cmd = "select * from meis where "
		cmd += "resname='" + resName + "' and ("
		cmd += "status='charged' or status='cash takeout')"
		cmd += " order by order_id asc"
		cmd += ";"
		result = self.query(cmd)

		self.crashReportClose('getAllDayOrders')
		return result


	def getAllDayOrders(self, resName, today):
		self.crashReportOpen('getAllDayOrders')

		cmd = "select * from meis where "
		cmd += "date='" + today + "' and "
		cmd += "resname='" + resName + "' and status<>'customer_login'"
		cmd += " order by order_id desc"
		cmd += ";"
		result = self.query(cmd)

		self.crashReportClose('getAllDayOrders')
		return result


	def getAllManagerOrders(self, resName, today):
		self.crashReportOpen('getAllManagerOrders')

		cmd = "select * from meis where "
		cmd += "date='" + today + "' and "
		cmd += "order_detail<>'unknown' and "
		cmd += "resname='" + resName + "'"
		cmd += " order by order_id desc;"

		self.crashReportClose('getAllManagerOrders')
		return self.query(cmd)

	def getAllWaiterOrders(self, resName, today, waiterName):
		self.crashReportOpen('getAllWaiterOrders')

		cmd = "select * from meis where "
		cmd += "date='" + today + "' and "
		cmd += "(entry_by='" + waiterName + "' or extra_info='" + waiterName + "' or status='kiosk-request') and "
		cmd += "resname='" + resName + "'"
		cmd += " order by order_id desc;"

		self.crashReportClose('getAllWaiterOrders')
		return self.query(cmd)

	def getRestaurantInfo(self, stuff, resName):
		self.crashReportOpen('getRestaurantInfo')

		cmd = "select " + stuff + " from res_dat where resname='" + resName + "';"
		result = self.query(cmd)
	
		self.crashReportClose('getRestaurantInfo')

		if len(result) == 0: return {}
		else: return result[0]


	def getAccount(self, resname, email):
		self.crashReportOpen('getAccount')

		cmd = "select * from customer_accounts where "
		cmd += "resname='" + resname + "' and "
		cmd += "email='" + email + "';"

		result = self.query(cmd)

		self.crashReportClose('getAccount')
		if len(result) == 0: return {}
		else: return result[0]



	def getAllOrderInfoToday(self, today):
		self.crashReportOpen('getAllOrderInfoToday')

		cmd = "select * from meis where "
		cmd += "date='" + today + "' and "
		cmd += "submit_source_type<>'unknown';"
		val = self.query(cmd)

		self.crashReportClose('getAllOrderInfoToday')
		return val

